The index is being ignored

If you are here, you have already:

• Run your SQL through Explain Plan and confirmed that the desired index is not being used.
• Checked the statistics on the underlying table.
• Confirmed that the index exists.

Oracle will ignore an index for a number of reasons, but they boil down to two possibilities:

• The structure of the SQL is such that Oracle cannot use an index
• Oracle's cost based optimizer is electing not to use the index

To find out which of these is true for your SQL, add an INDEX hint to your SQL.
eg.

SELECT /*+ INDEX(a, i_empno) */ a.* FROM emp a
WHERE a.emp_no = 111

Now run the SQL through Explain Plan. Is the index being used now? If the index is being used in a FULL SCAN, then this is pointless: it is just reading every row from the index. If the index is now being used with a UNIQUE or RANGE scan, it means that the Cost Based Optimiser doesn't think the index is very useful. Have you checked the statistics? Try running the SQL again; is the performance better now? If the performance is still poor, then Oracle was probably right to ignore the index - you almost certainly have a Range Scan problem.

If the INDEX hint did not work (ie. Oracle is still using Full Table Scan, Full Index Scan, or a different index), then there is something about the SQL preventing the index from being used.

• How are indexed column(s) used in the WHERE clause?
• Are they altered by functions, arithmetic, concatenation?
• Are they used with NOT or != operators?
• Are you using the index within OR or NOT predicates?
• If the index is a multi-column (concatenated) index, are you using the leading columns?
• Are you selecting from a view, or a nested query?
• Do you have a two table join? What order are the tables joined in?
• Are you trying to use Bitmap Indexes?
• Do you have a star schema query? Are you using Oracle's star query techniques?
• Are you casting an indexed column from a string to a number?
• Is the indexed column used with a sub-query clause?
  eg.
  • WHERE col IN (SELECT ...)
  • WHERE col = (SELECT ...)
  • WHERE col >= ANY (SELECT ...)
  • WHERE col <= ALL (SELECT ...)
  • WHERE col NOT IN (SELECT ...)

One of the bullet points above should determine why the index is not being used. If not, then work on the process of elimination. First confirm that oracle recognises the index - run the following through Explain Plan

SELECT /*+ INDEX(a, index_name)*/ *
FROM table_name a
WHERE index_col = :val

If this simple SQL refuses to use the index, then there is a fundamental problem. Get the DBA to drop and rebuild the index.

If the sample SQL above does use the index, then start with your original SQL and remove lines one at a time, running each successively smaller SQL through Explain Plan as you go. Somewhere between the original SQL and the one above, the index will start to work. Once you find the problem, check the bullet points above to see whether any of them explain the fault. If not, discuss the problem with the DBA.